using Microsoft.Data.Sqlite;
using Microsoft.Extensions.Logging;
using System;
using System.IO;
using DesktopHelper.Models;
using System.Collections.Generic;
using System.Threading.Tasks;

namespace DesktopHelper.Data
{
    public class DatabaseService
    {
        private readonly ILogger<DatabaseService> _logger;
        private readonly string _connectionString;
        private readonly string _dbPath;

        public DatabaseService(ILogger<DatabaseService> logger)
        {
            _logger = logger;
            
            // 数据库存储在用户应用数据目录
            var appDataPath = Path.Combine(
                Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData), 
                "DesktopHelper");
            
            Directory.CreateDirectory(appDataPath);
            _dbPath = Path.Combine(appDataPath, "DesktopHelper.db");
            _connectionString = $"Data Source={_dbPath}";
        }

        public void Initialize()
        {
            try
            {
                using var connection = new SqliteConnection(_connectionString);
                connection.Open();

                // 创建剪贴板历史表
                var createClipboardTable = @"
                    CREATE TABLE IF NOT EXISTS ClipboardHistory (
                        Id INTEGER PRIMARY KEY AUTOINCREMENT,
                        Type TEXT NOT NULL,
                        Content TEXT,
                        ImageData BLOB,
                        Thumbnail BLOB,
                        CreateTime DATETIME DEFAULT CURRENT_TIMESTAMP,
                        IsFavorite BOOLEAN DEFAULT FALSE,
                        Hash TEXT
                    )";

                // 创建钉图表
                var createPinnedImagesTable = @"
                    CREATE TABLE IF NOT EXISTS PinnedImages (
                        Id INTEGER PRIMARY KEY AUTOINCREMENT,
                        ImagePath TEXT NOT NULL,
                        X INTEGER,
                        Y INTEGER,
                        Width INTEGER,
                        Height INTEGER,
                        Opacity REAL DEFAULT 1.0,
                        CreateTime DATETIME DEFAULT CURRENT_TIMESTAMP,
                        IsVisible BOOLEAN DEFAULT TRUE
                    )";

                using var command1 = new SqliteCommand(createClipboardTable, connection);
                command1.ExecuteNonQuery();

                using var command2 = new SqliteCommand(createPinnedImagesTable, connection);
                command2.ExecuteNonQuery();

                _logger.LogInformation("数据库初始化完成: {DbPath}", _dbPath);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "数据库初始化失败");
                throw;
            }
        }

        public async Task<int> InsertClipboardItemAsync(ClipboardItem item)
        {
            try
            {
                using var connection = new SqliteConnection(_connectionString);
                await connection.OpenAsync();

                var sql = @"
                    INSERT INTO ClipboardHistory (Type, Content, ImageData, Thumbnail, CreateTime, IsFavorite, Hash)
                    VALUES (@Type, @Content, @ImageData, @Thumbnail, @CreateTime, @IsFavorite, @Hash);
                    SELECT last_insert_rowid();";

                using var command = new SqliteCommand(sql, connection);
                command.Parameters.AddWithValue("@Type", item.Type.ToString());
                command.Parameters.AddWithValue("@Content", item.Content ?? (object)DBNull.Value);
                command.Parameters.AddWithValue("@ImageData", item.ImageData ?? (object)DBNull.Value);
                command.Parameters.AddWithValue("@Thumbnail", item.Thumbnail ?? (object)DBNull.Value);
                command.Parameters.AddWithValue("@CreateTime", item.CreateTime);
                command.Parameters.AddWithValue("@IsFavorite", item.IsFavorite);
                command.Parameters.AddWithValue("@Hash", item.Hash ?? (object)DBNull.Value);

                var result = await command.ExecuteScalarAsync();
                return Convert.ToInt32(result);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "插入剪贴板记录失败");
                throw;
            }
        }

        public async Task<List<ClipboardItem>> GetClipboardHistoryAsync(int limit = 100)
        {
            try
            {
                using var connection = new SqliteConnection(_connectionString);
                await connection.OpenAsync();

                var sql = "SELECT * FROM ClipboardHistory ORDER BY CreateTime DESC LIMIT @Limit";
                using var command = new SqliteCommand(sql, connection);
                command.Parameters.AddWithValue("@Limit", limit);

                var items = new List<ClipboardItem>();
                using var reader = await command.ExecuteReaderAsync();

                while (await reader.ReadAsync())
                {
                    var item = new ClipboardItem
                    {
                        Id = reader.GetInt32(0), // Id
                        Type = Enum.Parse<ClipboardDataType>(reader.GetString(1)), // Type
                        Content = reader.IsDBNull(2) ? null : reader.GetString(2), // Content
                        ImageData = reader.IsDBNull(3) ? null : (byte[])reader.GetValue(3), // ImageData
                        Thumbnail = reader.IsDBNull(4) ? null : (byte[])reader.GetValue(4), // Thumbnail
                        CreateTime = reader.GetDateTime(5), // CreateTime
                        IsFavorite = reader.GetBoolean(6), // IsFavorite
                        Hash = reader.IsDBNull(7) ? null : reader.GetString(7) // Hash
                    };
                    items.Add(item);
                }

                return items;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "获取剪贴板历史失败");
                throw;
            }
        }

        public async Task DeleteClipboardItemAsync(int id)
        {
            try
            {
                using var connection = new SqliteConnection(_connectionString);
                await connection.OpenAsync();

                var sql = "DELETE FROM ClipboardHistory WHERE Id = @Id";
                using var command = new SqliteCommand(sql, connection);
                command.Parameters.AddWithValue("@Id", id);

                await command.ExecuteNonQueryAsync();
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "删除剪贴板记录失败");
                throw;
            }
        }

        public async Task<int> InsertPinnedImageAsync(PinnedImage image)
        {
            try
            {
                using var connection = new SqliteConnection(_connectionString);
                await connection.OpenAsync();

                var sql = @"
                    INSERT INTO PinnedImages (ImagePath, X, Y, Width, Height, Opacity, CreateTime, IsVisible)
                    VALUES (@ImagePath, @X, @Y, @Width, @Height, @Opacity, @CreateTime, @IsVisible);
                    SELECT last_insert_rowid();";

                using var command = new SqliteCommand(sql, connection);
                command.Parameters.AddWithValue("@ImagePath", image.ImagePath);
                command.Parameters.AddWithValue("@X", image.X);
                command.Parameters.AddWithValue("@Y", image.Y);
                command.Parameters.AddWithValue("@Width", image.Width);
                command.Parameters.AddWithValue("@Height", image.Height);
                command.Parameters.AddWithValue("@Opacity", image.Opacity);
                command.Parameters.AddWithValue("@CreateTime", image.CreateTime);
                command.Parameters.AddWithValue("@IsVisible", image.IsVisible);

                var result = await command.ExecuteScalarAsync();
                return Convert.ToInt32(result);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "插入钉图记录失败");
                throw;
            }
        }
    }
}